package org.kunteng.yunac.util;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Set;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import redis.clients.jedis.Jedis;

public class RedisService {
  public static void main(String[] args) {
    Jedis jedis = RedisUtil.getJedis();
//    syncChannel(jedis);
    syncMac(jedis);
//    syncChannelAuth(jedis);
//    syncPortalConf(jedis);
//    syncPortalPage(jedis, "1");
//    syncPortalPage(jedis, "2");
//    syncPortalPage(jedis, "3");
//    syncPortalPage(jedis, "4");
//    syncRealizePageUrl(jedis);
//    syncPortalDuration(jedis);
    RedisUtil.returnResource(jedis);
  }

  // 同步渠道
  public static void syncChannel(Jedis jedis) {
    Connection conn = new DBConn().getConn();
    try {
      Statement st = conn.createStatement();
      String sql = "SELECT c.id,c.pid,c.type,c.code FROM channel c WHERE c.id<>'1'";
      ResultSet re = st.executeQuery(sql);
      while (re.next()) {
        String id = re.getString("id");
        String pid = re.getString("pid");
        String type = String.valueOf(re.getInt("type"));
        String path = re.getString("code").replaceAll("#", "_");
        String key = "channel:" + id + ":parent";
        if ("0".equals(type)) {
          type = "1";
        } else {
          type = "2";
        }
        String key1 = "channel:" + id + ":type";
        String key2 = "channel:" + id + ":path";
//        System.out.println(key + " => " + pid);
//        System.out.println(key1 + " => " + type);
//        System.out.println(key2 + " => " + path);
        
        jedis.set(key, pid);
        jedis.set(key1, type);
        jedis.set(key2, path);
        System.out.println(key + " -> " + jedis.get(key));
        System.out.println(key1 + " -> " + jedis.get(key1));
        System.out.println(key2 + " -> " + jedis.get(key2));
      }

      st.close();
      conn.close();

    } catch (Exception e) {
      e.printStackTrace();
    }
  }
  
  // 同步路由
  public static void syncMac(Jedis jedis) {
    Connection conn = new DBConn().getConn();
    try {
      Statement st = conn.createStatement();
      String sql = "SELECT r.mac,c.id "
                      + "FROM (SELECT rd.mac,MAX(c.weight) weight FROM router_distribute rd,channel c WHERE rd.cid=c.id GROUP BY mac) rdm,"
                      + "channel c,router r,router_distribute rd "
                      + "WHERE c.weight=rdm.weight AND r.mac=rd.mac AND rd.cid=c.id AND rd.mac=rdm.mac";
      ResultSet re = st.executeQuery(sql);
      while (re.next()) {
        String mac = re.getString("mac");
        String channel_id = re.getString("id");
        String key = "mac:" + mac;
//        System.out.println(key + " => " + channel_id);

        jedis.set(key, channel_id);
        System.out.println(key + " -> " + jedis.get(key));
      }

      st.close();
      conn.close();

    } catch (Exception e) {
      e.printStackTrace();
    }
  }
  
  // 同步渠道权限
  public static void syncChannelAuth(Jedis jedis) {
    Connection conn = new DBConn().getConn();
    Connection conn1 = new DBConn().getConn();
    try {
      Statement st = conn.createStatement();
      Statement st1 = conn1.createStatement();
      String sql = "SELECT cid FROM channel_has_sysauth WHERE cid<>'1' GROUP BY cid";
      ResultSet re = st.executeQuery(sql);
      while (re.next()) {
        String cid = re.getString("cid");
        Set<String> set = new HashSet<String>();
        sql = "SELECT DISTINCT auth_id FROM channel_has_sysauth WHERE cid = '" + cid + "'";
        ResultSet re1 = st1.executeQuery(sql);
        while (re1.next()) {
          String auth_id = re1.getString("auth_id");
          set.add(auth_id);
        }
        String key = "channel:" + cid + ":permission";
//        System.out.println(key + " => " + set);
        
        jedis.sadd(key, setToArray(set));
        System.out.println(key + " -> " + jedis.smembers(key));
      }

      st1.close();
      st.close();
      conn1.close();
      conn.close();

    } catch (Exception e) {
      e.printStackTrace();
    }
  }
  
  // 同步认证页配置信息
  public static void syncPortalConf(Jedis jedis) {
    Connection conn = new DBConn().getConn();
    try {
      Statement st = conn.createStatement();
      String sql = "SELECT * FROM portal_conf";
      ResultSet re = st.executeQuery(sql);
      while (re.next()) {
        String cid = re.getString("cid");
        String key = "channel:"+cid+":conf";
        int onekey = re.getInt("web");
        int sms = re.getInt("phone");
        int wechat = re.getInt("app");
        if (onekey  == 1) {
//          System.out.println(key + " => onekey => " + onekey);
          jedis.hset(key, "onekey", String.valueOf(onekey));
          System.out.println(cid + " -> onekey -> " + jedis.hget(key, "onekey"));
        }
        if (sms  == 1) {
//          System.out.println(key + " => sms => " + sms);
          jedis.hset(key, "sms", String.valueOf(sms));
          System.out.println(cid + " -> sms -> " + jedis.hget(key, "sms"));
        }
        if (wechat  == 1) {
//          System.out.println(key + " => wechat => " + wechat);
          jedis.hset(key, "wechat", String.valueOf(wechat));
          System.out.println(cid + " -> wechat -> " + jedis.hget(key, "wechat"));
          
          StringBuffer sb = new StringBuffer();
          String stores = re.getString("stores").replace("&quot;", "\"");
          JSONArray  jsonArray = JSONArray.fromObject(stores);
          for (Iterator<?> iter = jsonArray.iterator(); iter.hasNext();) {
            JSONObject  jb = (JSONObject )iter.next();
            String shopId = jb.getString("SHOP_ID");
            String ssid = jb.getString("SSID");
            String secretKey = jb.getString("secretKey");
            sb.append(shopId+"|"+ssid+"|"+secretKey);
            sb.append(",");
          }
          String wechat_shop = sb.toString();
          wechat_shop = wechat_shop.substring(0, wechat_shop.length()-1);
          
//          System.out.println(key + " => wechat.appid => " + re.getString("appid"));
//          System.out.println(key + " => wechat.appsecret => " + re.getString("appsecret"));
//          System.out.println(key + " =>  wechat.shop => " + wechat_shop);
          
          jedis.hset(key, "wechat.appid", re.getString("appid"));
          jedis.hset(key, "wechat.appsecret", re.getString("appsecret"));
          jedis.hset(key, "wechat.shop", wechat_shop);
          
          System.out.println(cid + " -> wechat.appid -> " + jedis.hget(key, "wechat.appid"));
          System.out.println(cid + " -> wechat.appsecret -> " + jedis.hget(key, "wechat.appsecret"));
          System.out.println(cid + " -> wechat.shop -> " + jedis.hget(key, "wechat.shop"));
        }
      }

      st.close();
      conn.close();

    } catch (Exception e) {
      e.printStackTrace();
    }
  }
  
  // 同步认证页广告图片信息
  public static void syncPortalPage(Jedis jedis, String num) {
    Connection conn = new DBConn().getConn();
    try {
      Statement st = conn.createStatement();
      String sql = "SELECT * FROM portal_page" + num;
      ResultSet re = st.executeQuery(sql);
      while (re.next()) {
        String id = re.getString("id");
        String pic = re.getString("pic").replace("/static/portals/", "");
        String key = "channel:" + id + ":page" + num;
//        System.out.println(key + " => " + pic);

        jedis.set(key, pic);
        System.out.println(key + " -> " + jedis.get(key));
      }

      st.close();
      conn.close();

    } catch (Exception e) {
      e.printStackTrace();
    }
  }
  
  // 同步渠道变现页面地址信息
  public static void syncRealizePageUrl(Jedis jedis) {
    Connection conn = new DBConn().getConn();
    try {
      Statement st = conn.createStatement();
      String sql = "SELECT * FROM realize_page_url";
      ResultSet re = st.executeQuery(sql);
      while (re.next()) {
        String cid = re.getString("cid");
        String url = re.getString("url");
        String key = "channel:" + cid + ":adurl";
//        System.out.println(key + " => " + url);

        jedis.set(key, url);
        System.out.println(key + " -> " + jedis.get(key));
      }

      st.close();
      conn.close();

    } catch (Exception e) {
      e.printStackTrace();
    }
  }
  
  // 同步时长参数设定
  public static void syncPortalDuration(Jedis jedis) {
    Connection conn = new DBConn().getConn();
    try {
      Statement st = conn.createStatement();
      String sql = "SELECT * FROM portal_duration";
      ResultSet re = st.executeQuery(sql);
      while (re.next()) {
        String id = re.getString("id");
        String duration = re.getString("duration");
        String key = "channel:" + id + ":duration";
//         System.out.println(key + " => " + duration);

        jedis.set(key, duration);
        System.out.println(key + " -> " + jedis.get(key));
      }

      st.close();
      conn.close();

    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  // set转数组
  public static String[] setToArray(Set<String> set) {
    String[] array = new String[set.size()];
    int i = 0;
    for (String str : set) {
      array[i] = str;
      i++;
    }
    return array;
  }

  // 数组转字符串
  public static String arrayToStr(String[] array) {
    String str = "";
    for (int i = 0; i < array.length; i++) {
      str += array[i];
      if (i != array.length - 1) {
        str += ",";
      }
    }
    return str;
  }

}
